package com.spring.task;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Service;

import com.admin.model.ChainGw;
import com.admin.model.ChainGwCx;
import com.admin.util.ChainUtil;
import com.admin.util.OrcUtil;
import com.cxtj.CxBean;
import com.cxtj.CxtjBean;
import com.cxtj.CxtjDAO;
import com.cxtj.EtlBean;

@Service("ChainorcDao")
public class ChainorcDao extends JdbcDaoSupport{

	@Resource
	public void setJb(JdbcTemplate jdbctemplateorc_216) {
		super.setJdbcTemplate(jdbctemplateorc_216);
	}
	
	public int getCountGwcx() {
		return this.getJdbcTemplate().queryForInt("select count(*) from v$sql");
	}
	
	 public void getDbSql(){
		// String sql = "select * from EMP";
    	String sql = "SELECT USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE USERNAME not in ('DBSNMP','SYSMAN') ORDER BY TIMESTAMP DESC";  
    	 List<Map<String, Object>> resList=this.getJdbcTemplate().queryForList(sql);
         for(Map<String, Object> rs:resList){                               
  //         System.out.println(rs.getString("ENAME"));        
             System.out.print(rs.get("USERNAME").toString()+"\t");    
             System.out.print(rs.get("TIMESTAMP").toString()+"\t"); 
             System.out.println(rs.get("SQL_TEXT").toString());        
            }  
	}
	
	 public List<CxBean> getCxList(String minTime,String maxTime) throws SQLException{
	    	String sql = "SELECT OBJ_NAME,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE  owner not in ('DBSNMP','SYS','PUBLIC') and OBJ_NAME is not null "; 
	    	if(minTime!=null && !"".equals(minTime) && maxTime!=null && !"".equals(maxTime)){
	    		sql = sql + " and Timestamp<to_date('"+maxTime+"', 'yyyy-mm-dd hh24:mi:ss') and Timestamp>to_date('"+minTime+"', 'yyyy-mm-dd hh24:mi:ss') ";  
	    	}else{
	    		if(minTime!=null && !"".equals(minTime)){
	    			sql = sql + " and Timestamp>to_date('"+minTime+"', 'yyyy-mm-dd hh24:mi:ss') and USERNAME not in ('DBSNMP','SYSMAN','BPM2015') "; 
		    	}else if(maxTime!=null && !"".equals(maxTime)){
		    		sql = sql + " and Timestamp<to_date('"+maxTime+"', 'yyyy-mm-dd hh24:mi:ss')  and USERNAME not in ('DBSNMP','SYSMAN','BPM2015') "; 
		    	}
	    	}
	    	sql = sql +" ORDER BY TIMESTAMP DESC";
	    	System.out.println(sql);
	        List<Map<String, Object>> rs = this.getJdbcTemplate().queryForList(sql); 
	       //定义存放查询结果的结果集          
	        List<CxBean> cxList = new ArrayList<CxBean>();	         
	            for(Map<String, Object> map:rs){ 
		             CxBean cx = new CxBean();             
		             cx.setSqluser(map.get("USERNAME").toString());    
		             cx.setOBJ_NAME(map.get("OBJ_NAME").toString());    
		             if(map.get("TIMESTAMP")!=null && !"".equals(map.get("TIMESTAMP"))){
		            	 cx.setCzsj(Timestamp.valueOf(map.get("TIMESTAMP").toString())); 
		             }
		          // cx.setDycs(rs.getInt("OBJ_NAME"));
		             if(map.get("SQL_TEXT")==null || "".equals(map.get("SQL_TEXT")))
		             {
		            	 cx.setConsql("null");
		             }
		             else{
		            	 cx.setConsql(map.get("SQL_TEXT").toString());    
		             }
		             cxList.add(cx);
	            }
	      //  System.out.println("total:"+cxList.size()+" || MinTime:"+cxList.get(cxList.size()-1).getCzsj());  	       
			return cxList;	    	
	    }
	 public List<CxBean> getCxListByTableAndUser(List<EtlBean> tbs) throws SQLException{	
		 	String allSQL="SELECT OBJ_NAME,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL where TIMESTAMP>sysdate ";
	    	if(tbs!=null && tbs.size()>0) {	    
		    	for(EtlBean tb:tbs){
			    	StringBuffer sql=new StringBuffer("SELECT OBJ_NAME,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL where USERNAME not in ('DBSNMP','SYSMAN','BPM2015') "); 
			    	if(tb.getTable_name()!=null && !"".equals(tb.getTable_name())){
			    		sql.append(" and OBJ_NAME = '"+tb.getTable_name()+"' ");
			    	}
			    	if(tb.getData_time()!=null && !"".equals(tb.getData_time())){
			    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			    		String dateStr = sdf.format(tb.getData_time());
			    		System.out.println("表："+tb.getTable_name()+" 最大数据时间："+dateStr+" 用户："+tb.getUser_name());
			    			sql.append(" and Timestamp>to_date('"+dateStr+"', 'yyyy-mm-dd hh24:mi:ss') "); 
				    	}
			    	if(tb.getUser_name()!=null && !"".equals(tb.getUser_name())){
			    		sql.append(" and USERNAME = '"+tb.getUser_name()+"' ");
			    	}
			    	allSQL=allSQL+" union "+sql;
			    	//	sql.append(" ORDER BY TIMESTAMP DESC ");
		    	}
	    	}
	    	else return null;
	    	System.out.println("getCxListByTableName:"+allSQL);
	        List<Map<String, Object>> rs = this.getJdbcTemplate().queryForList(allSQL); 
	       //定义存放查询结果的结果集          
	        List<CxBean> cxList = new ArrayList<CxBean>();	         
	            for(Map<String, Object> map:rs){ 
		             CxBean cx = new CxBean();             
		             cx.setSqluser(map.get("USERNAME").toString());    
		             cx.setOBJ_NAME(map.get("OBJ_NAME").toString());    
		             if(map.get("TIMESTAMP")!=null && !"".equals(map.get("TIMESTAMP"))){
		            	 cx.setCzsj(Timestamp.valueOf(map.get("TIMESTAMP").toString())); 
		             }
		          // cx.setDycs(rs.getInt("OBJ_NAME"));
		             if(map.get("SQL_TEXT")==null || "".equals(map.get("SQL_TEXT")))
		             {
		            	 cx.setConsql("null");
		             }
		             else{
		            	 cx.setConsql(map.get("SQL_TEXT").toString());    
		             }
		             cxList.add(cx);
	            }	       
			return cxList;	    	
	    }
	 public int countRecord(String tablename,String username){
		 String cntSQL ="select count(1) from etl_table DBA_AUDIT_TRAIL where USERNAME not in ('DBSNMP','SYSMAN','BPM2015')  AND OBJ_NAME = '"+tablename+"' and USERNAME='"+username+"' ";
		int cnt = this.getJdbcTemplate().queryForInt(cntSQL);
		return 0;
		 
	 }
}
